/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sistema.script;

import com.interact.sas.cmn.db.services.QueryResult;
import com.interact.sas.cmn.db.services.Database;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author gr
 */
public class ScriptImportLigacoes
{

    Connection conn = null;
    String user = "root";
    String pass = "";
    String url = "jdbc:mysql://localhost/controle_ligacoes";
    String[] mnemonics =
    {
        "lig_recebida",
        "lig_realizada"
    };

    public ScriptImportLigacoes()
    {
        doWorkTC();
    }

    void doWork()
    {

        for ( String mnemonic : mnemonics )
        {

            String sql = "SELECT a.id, b.dt_meta "
                    + "FROM bsc_indicators a "
                    + "INNER JOIN bsc_items b "
                    + "ON ( a.id = b.ref_indicator ) "
                    + "WHERE a.mnemonic = '" + mnemonic + "' "
                   // + "AND b.dt_result is null "
//                    + "ORDER BY b.dt_meta limit 1 ";
                    + "ORDER BY b.dt_meta ";

            try
            {

                QueryResult buscaIndicador = Database.getInstance().executeQuery( sql );
                List<String>datasMetas = new ArrayList<String>();
                datasMetas.clear();


                while ( buscaIndicador.next() )
                {
                    int id_indicador = buscaIndicador.getInt( 1 );
                    String dt_meta = buscaIndicador.getString( 2 );
                    datasMetas.add( dt_meta );
                    int x = 0;


                    if( datasMetas.size() < 2 )
                    {
                         x = buscaTotalLigacao( mnemonic, dt_meta, dt_meta );
                    }
                     else
                    {
                         x = buscaTotalLigacao( mnemonic, datasMetas.get( datasMetas.size() - 2 ), dt_meta );
                    }

                    Database.getInstance().executeCommand( "UPDATE bsc_items set result= " + x + ", dt_result = current_date WHERE dt_meta = '" + dt_meta + "' and ref_indicator= "+ id_indicador +"" );

                }


            } catch ( Exception err )
            {
                err.printStackTrace();
            }

        }



    }

    int buscaTotalLigacao( String mnemonico, String dt1, String dt2 )
    {

//        String sqlTotal = "SELECT sum( " + mnemonico + " ) as valor "
//                + "FROM ligacoes "
//                + "WHERE week(data) = week('" + dt + "')";

        String sqlTotal = "SELECT sum( " + mnemonico + " ) as valor "
                        + "FROM ligacoes "
                        + "WHERE data between '"+ dt1 +"' and '"+ dt2 +"' ";

        int total = 0;


        try
        {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery( sqlTotal );

            while ( rs.next() )
            {
                total = rs.getInt( "valor" );
                print( "Total de " + mnemonico + " " + total +" data: "+ dt2);
            }

        } catch ( Exception ee )
        {
            ee.printStackTrace();
        }

        return total;
    }

    void doWorkTC()
    {
        connectDatabase();
        doWork();
    }

    void connectDatabase()
    {
        if ( conn == null )
        {
            try
            {
                Class.forName( "com.mysql.jdbc.Driver" );
                conn = DriverManager.getConnection( url, user, pass );


            } catch ( Exception e )
            {
                e.printStackTrace();
            }
        }
    }

    public void print( String args )
    {
        System.out.println( args );
    }

    public static void main( String args[] )
    {
        new ScriptImportLigacoes();
    }
}
